-- EXEC SP_GET_COST_CODE_DETAILS 0, '0', '0', 0

ALTER PROC SP_GET_COST_CODE_DETAILS 
@PROCESSSTEP_ID INT,
@PACKAGE_PARAMETER VARCHAR(10),
@PIN_PARAMETER VARCHAR(10),
@WITHOUTCOSTCODEPRODUCTS INT
AS
BEGIN
	DECLARE @ASSEMBLY_PROCESSSTEP_ID INT
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @BASE_PRODUCT VARCHAR(50)
	DECLARE @PACKAGE_TYPE VARCHAR(10)
	DECLARE @PIN_TYPE VARCHAR(10)
	DECLARE @COST_CODE VARCHAR(10)
	DECLARE @CC_CODE VARCHAR(10)
	DECLARE @FG_PROCESSSTEP_ID INT 
	DECLARE @SQL_STRING VARCHAR(800)
	DECLARE @RESULTS TABLE(PROCESSSTEP_ID INT, PARTNUMBER VARCHAR(50), BASEPRODUCT VARCHAR(50),PACKAGENAME VARCHAR(10), PINNUMBER VARCHAR(10),COSTCODE VARCHAR(10),CC_CODE VARCHAR(10))

	IF (@PROCESSSTEP_ID != 0)
	BEGIN
		SELECT @ASSEMBLY_PROCESSSTEP_ID=PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BACKWARD_BY_ID (@PROCESSSTEP_ID) WHERE PS_TYPE = 'ASSEMBLY'
	
		SELECT  @PACKAGE_TYPE=PKG.PARAMETER_VALUE, @PIN_TYPE=PIN.PARAMETER_VALUE FROM PROCESS_PARAMETER PKG, PROCESS_PARAMETER PIN, PROCESS_STEP PS WHERE PKG.PARAMETER_KEY = 'PACKAGE ID' AND PIN.PARAMETER_KEY = 'PIN' AND PKG.PROCESSSTEP_ID = PIN.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = PKG.PROCESSSTEP_ID AND PS.PS_TYPE = 'ASSEMBLY'
		AND PKG.PROCESSSTEP_ID=@ASSEMBLY_PROCESSSTEP_ID
	
		SELECT @PART_NUMBER = PART_NUMBER, @COST_CODE=COST_CODE FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		SELECT @BASE_PRODUCT = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = ( SELECT P_PARENT_PRODUCT_ID FROM PRODUCT WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID)
		IF ( @COST_CODE IS NOT NULL )
		BEGIN
			SELECT @CC_CODE = CC_ID FROM COST_CODE_TABLE WHERE COST_CODE = @COST_CODE
		END
		INSERT INTO @RESULTS( PROCESSSTEP_ID, PARTNUMBER, BASEPRODUCT,PACKAGENAME, PINNUMBER, COSTCODE, CC_CODE )
		VALUES( @PROCESSSTEP_ID, @PART_NUMBER, @BASE_PRODUCT,@PACKAGE_TYPE,@PIN_TYPE,@COST_CODE, @CC_CODE )
	END
	ELSE
	BEGIN
		SET @SQL_STRING = 'DECLARE COST_CODE_CURSOR CURSOR FOR SELECT  PS.PROCESSSTEP_ID, PKG.PARAMETER_VALUE, PIN.PARAMETER_VALUE FROM PROCESS_PARAMETER PKG, PROCESS_PARAMETER PIN, PROCESS_STEP PS WHERE PKG.PARAMETER_KEY = ''PACKAGE ID'' AND PIN.PARAMETER_KEY = ''PIN'' AND PKG.PROCESSSTEP_ID = PIN.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = PKG.PROCESSSTEP_ID AND PS.PS_TYPE = ''ASSEMBLY'''
		
		IF ( @PACKAGE_PARAMETER IS NOT NULL AND @PACKAGE_PARAMETER != '0' )
		BEGIN
			SET @SQL_STRING = @SQL_STRING +' AND PKG.PARAMETER_VALUE =''' + @PACKAGE_PARAMETER + ''''
		END
		IF ( @PIN_PARAMETER IS NOT NULL AND @PIN_PARAMETER != '0' )
		BEGIN
			SET @SQL_STRING =@SQL_STRING + ' AND PIN.PARAMETER_VALUE =''' +  @PIN_PARAMETER + ''''
		END
		EXEC (@SQL_STRING)
		
		OPEN COST_CODE_CURSOR
		FETCH NEXT FROM COST_CODE_CURSOR INTO @ASSEMBLY_PROCESSSTEP_ID, @PACKAGE_TYPE, @PIN_TYPE
		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @FG_CURSOR CURSOR 
			SET @FG_CURSOR = CURSOR FOR SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@ASSEMBLY_PROCESSSTEP_ID,1) WHERE PS_TYPE_NAME = 'FINISHED GOODS' 	
			
			OPEN @FG_CURSOR
			FETCH NEXT FROM @FG_CURSOR INTO @FG_PROCESSSTEP_ID
			WHILE @@FETCH_STATUS = 0
		 	BEGIN
				SELECT @PART_NUMBER = PART_NUMBER , @COST_CODE = COST_CODE FROM PRODUCT WHERE PROCESSSTEP_ID = @FG_PROCESSSTEP_ID		
				SELECT @BASE_PRODUCT = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = ( SELECT P_PARENT_PRODUCT_ID FROM PRODUCT WHERE PROCESSSTEP_ID = @FG_PROCESSSTEP_ID)
				IF (@COST_CODE!= NULL)
				BEGIN
					SELECT @CC_CODE=CC_ID FROM COST_CODE_TABLE WHERE COST_CODE=@COST_CODE
				END				
				INSERT INTO @RESULTS ( PROCESSSTEP_ID, PARTNUMBER, BASEPRODUCT, PACKAGENAME, PINNUMBER, COSTCODE, CC_CODE ) 
				VALUES (@FG_PROCESSSTEP_ID, @PART_NUMBER, @BASE_PRODUCT, @PACKAGE_TYPE, @PIN_TYPE, @COST_CODE, @CC_CODE)

				SET @PART_NUMBER = NULL
				SET @COST_CODE = NULL 
				SET @CC_CODE = NULL
				SET @FG_PROCESSSTEP_ID = NULL
				FETCH NEXT FROM @FG_CURSOR INTO @FG_PROCESSSTEP_ID
		 	END
			CLOSE @FG_CURSOR
			DEALLOCATE @FG_CURSOR
	
			SET @ASSEMBLY_PROCESSSTEP_ID= NULL
			SET @PACKAGE_TYPE = NULL 
			SET @PIN_TYPE = NULL
			FETCH NEXT FROM COST_CODE_CURSOR INTO @ASSEMBLY_PROCESSSTEP_ID, @PACKAGE_TYPE, @PIN_TYPE
		END
		CLOSE COST_CODE_CURSOR
		DEALLOCATE COST_CODE_CURSOR
	END
	IF @WITHOUTCOSTCODEPRODUCTS=0
	BEGIN
 		SELECT PROCESSSTEP_ID, PARTNUMBER, BASEPRODUCT,PACKAGENAME, PINNUMBER, COSTCODE, CC_CODE from @results order by PACKAGENAME, PINNUMBER, BASEPRODUCT, PARTNUMBER 
	END
	ELSE
	BEGIN
		SELECT PROCESSSTEP_ID, PARTNUMBER, BASEPRODUCT,PACKAGENAME, PINNUMBER, COSTCODE, CC_CODE from @results WHERE COSTCODE IS NULL order by PACKAGENAME, PINNUMBER, BASEPRODUCT, PARTNUMBER 
	END 
END